<%@ page contentType="application/vnd.ms-excel; charset=UTF-8" language="java" %>
<%@ page language="java" import="com.base.myproject.client.jdbc.*" %>
<%@ page language="java" import="java.util.*" %>
<%@ page language="java" import="com.base.myproject.server.*" %>
<%@ page language="java" import="com.base.myproject.client.jdbc.DataSet" %>
<%@ page language="java" import="com.base.myproject.server.tools.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:x="urn:schemas-microsoft-com:office:excel"> 
<head>
<title></title>
<%
	response.setHeader("Content-disposition","attachment; filename=employer.xls");
	String id = request.getParameter("id");
	String name = request.getParameter("name");
	String[] names = name.split("@");
	String ids[] = id.split("@");
	String where=name.lastIndexOf("where") == -1 ? request.getParameter("where") : name.substring(name.lastIndexOf("where"));

	String sql = "select  bcode_job.employstatus, bcode.age, bcode.icode, bcode_job.leavereason, bcode.hr_City_Township, bcode.hr_name_Alias,bcode.bird,systeminfo.name as up_department,bcode.departments,bcode.sex ," +
	"bcode_job.Resignationdate, bcode_job.employinnertitle,bcode.departments as systemid,bcode_job.jobtype," +
	"bcode_job.jobproperty,bcode_job.jobgroup,bcode.bcode," +
	"bcode.hr_firstname_en,bcode.bname,bcode.hr_nationality," +
	"hr_bcode_contract.contract_begin_date contractstartdate,hr_bcode_contract.contract_end_date contractsenddate," +
	"bcode.hr_Marital_status,bcode_job.employlevel,bcode_job.groupregularizationdate,bcode_job.Insurancedate," +
	"bcode_job.attendancecardno,bcode_job.entrydate,(select top 1 addr  from hr_idCard  where type=(select name from dictionary where code = 'cd001') and bcode=bcode.bcode)" +
	" as hr_idcard_addr,(select top 1 idNo  from hr_idCard  where type=(select name from dictionary where code = 'cd001') and bcode=bcode.bcode) as idNo," +
	"bcode.hr_Census_Province,bcode.hr_Census_city,bcode_job.hostelno," +
	"bcode_job.lockersno,bcode.basesalary,bcode.hr_bankcard,bcode.hr_bankno," +
	"bcode_job.employoutertitle,bcode_job.Regularizationdate,bcode.hr_status," +
	"bcode_job.lastpaydate,bcode_job.lastjobdate,(select top 1 availableDate  from hr_idCard  where type=(select name from dictionary where code = 'cd001') and bcode=bcode.bcode) as hr_idcard_limitdate," +
	"bcode_job.employno,(select top 1 startdate from bcode_Allowance where bcode=bcode.bcode and no_ ='A01' and status=0 order by startdate desc)" +
	" as  syalsry_startdate,'ff' as m7 " +
	" from bcode inner join systeminfo on systeminfo.id = up_department left join bcode_job on bcode.bcode=bcode_job.bcode  left join (select * from hr_bcode_contract a where exists (select 1 from hr_bcode_contract where bcode = a.bcode group by bcode having max(contract_begin_date) = a.contract_begin_date)) as hr_bcode_contract on bcode.bcode = hr_bcode_contract.bcode "+where;
	DataSetTool DST = new DataSetTool();
	DataSet result = DST.getDataSet(sql);
%>
</head>
<body>
	<table style="border-collapse: collapse" cellspacing="0" cellpadding="0" border="1"  bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;">
		<tr>
			<%
				for(int i = 0;i < names.length;i++){
				%>
					<td><%= names[i]%></td>
				<%
				}
			%>
		</tr>  
		
			<%
				for(int i = 0;i < result.getRowCount();i++){
				%>
					<tr>
						<%
							for(int j = 0;j < ids.length;j++){
								if(ids[j].equals("idNo") || ids[j].equals("hr_bankcard")){
								%>
									<td x:str><%=result.getValue(i,""+ids[j]+"") == null ? "" : result.getValue(i,""+ids[j]+"") %></td>
								<%
								}else if(ids[j].equals("")){
								%>
									<td style="mso-number-format:'\@';"><%=result.getValue(i,""+ids[j]+"") == null ? "" : result.getValue(i,""+ids[j]+"") %></td>
								<%
								}else{
									%>
										<td><%=result.getValue(i,""+ids[j]+"") == null ? "" : result.getValue(i,""+ids[j]+"") %></td>
									<%
								}
							}
						%>
					</tr>
				<%
				}
			%>
		
	</table>

</body>
</html>